package com.wxtf.platform.main.jcqkgl;

import java.io.File;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;

/**
 * <li>Title: credentials_recordAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.credentials_recordAction</li>
 * <li>Description: 超生缴纳社会抚养费</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class exceeds_childrenAction extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(exceeds_childrenAction.class);

	public exceeds_childrenAction(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			}
			else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("insertData".equals(this.getMethodName())) {
				msg = this.insertData(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			} else if("listAll".equals(this.getMethodName())) {//子女列表
				msg = this.listAll(this.getLogicData());
				return msg;
			}
			else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(user.getUserrelName(), e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}
	
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		sql = " from trp_exceeds_children_penalty p LEFT JOIN tb_family_base f on f.tbfi_id=p.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id where 1=1";

		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("init".equals(types)) {// 初始查询
				sql += " and e.org_id in (" + user.getCurSubOrgId() + ")";
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String tbe_name = jsonObject.getString("tbe_name");//姓名
				String trpecp_payment_person = jsonObject.getString("trpecp_payment_person");//缴款人
				String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证号
				String time1 = jsonObject.getString("time1");// 开始时间
				String time2 = jsonObject.getString("time2");// 结束时间
				
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and e.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				if(StringUtils.isNotBlank(tbe_name)){
					buff.append(" and tbe_name like '%").append(tbe_name).append("%'");
				}
				if(StringUtils.isNotBlank(trpecp_payment_person)){
					buff.append(" and trpecp_payment_person like '%").append(trpecp_payment_person).append("%'");
				}
				if(StringUtils.isNotBlank(tbe_idcard)){
					buff.append(" and tbe_idcard like '%").append(tbe_idcard).append("%'");
				}
				if(StringUtils.isNotBlank(time1)) {
					buff.append(" and trpecp_payment_time >= '").append(time1).append("'");
				}
				if(StringUtils.isNotBlank(time2)) {
					buff.append(" and trpecp_payment_time <='").append(time2).append(" 23:59:59'");
				}
				sql += buff.toString();
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			if("reserve1".equals(sortname))
			{
				sql += " order by e." + sortname + " " + sortorder;
			}else
			{
				sql += " order by " + sortname + " " + sortorder;
			}
			
		}
		else
		{
			sql +=" order by p.last_modify_time desc";
		}
		
		String countSql = "select count(*) " + sql;
		String querySql = "SELECT p.trpecp_id,e.org_id,f.tbfi_id,e.tbe_name,e.tbe_sex,e.tbe_idcard,e.tbe_birthday,e.tbe_nation,f.tbfi_phone,f.tbfi_address,f.tbfi_peo_num,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,p.trpecp_ec_description,p.trpecp_exceeds_children_num,p.trpecp_penalty,p.trpecp_payment_person,p.trpecp_payment_time,p.trpecp_memo,e.reserve1,p.reserve2,p.reserve3,p.reserve4,f.reserve5,p.create_by,p.create_time,p.last_modify_by,p.last_modify_time " + sql;
		String[] fieldss = {"trpecp_id","org_id","tbfi_id","tbe_name","tbe_sex","tbe_idcard","tbe_birthday","tbe_nation","tbfi_phone","tbfi_address",
				"tbfi_peo_num","tbfi_children_num","tbfi_children_mnum","tbfi_children_fnum","trpecp_ec_description","trpecp_exceeds_children_num",
				"trpecp_penalty","trpecp_payment_person","trpecp_payment_time","trpecp_memo","reserve1", "reserve2", "reserve3", "reserve4", "reserve5", "create_by", "create_time",
				"last_modify_by", "last_modify_time" };
		//System.out.println(querySql);
		String strJson = AccessDataBaseUtil.query4SQLPagging(fieldss, countSql, querySql, this.getPage(), this.getPagesize(), null);
		String exceeds="select trpecp_penalty "+sql;
		List<Object> list= AccessDataBaseUtil.query4List(exceeds,null);
		float totals=0;
		if (list == null) {
			return strJson;
		} else{
			if(list.size() > 0) {
				for(int i = 0; i < list.size(); i++) {// 行
				Object trpecp_penalty= list.get(i);
				if(!trpecp_penalty.equals("") && trpecp_penalty != null) {
					totals += Float.parseFloat(trpecp_penalty.toString());
					}
				}
				return strJson.substring(0, strJson.indexOf(","))+",\"totals\":\"" + totals+ "\""+ strJson.substring(strJson.indexOf(","), strJson.length());
				
			}else{return strJson;}
		}
	}
	
	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String reserve5 = jsonObject.getString("reserve5");
		String tbfi_id = jsonObject.getString("tbfi_id");
		String sql_sel="select count(*) from trp_exceeds_children_penalty where (tbfi_id in (SELECT tbfi_id from tb_family_base where tbe_id = (SELECT tbe_spouse_id from tb_family_base where tbfi_id = '"+tbfi_id+"')) or tbfi_id = '"+tbfi_id+"')";
		if(AccessDataBaseUtil.queryForInt(sql_sel, null) != 0) {
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"该家庭已经登记过,不能再次登记\"}";
		}
		String org_id = jsonObject.getString("org_id");
		String trpecp_penalty = jsonObject.getString("trpecp_penalty");
		String trpecp_ec_description = jsonObject.getString("trpecp_ec_description");
		String trpecp_exceeds_children_num = jsonObject.getString("trpecp_exceeds_children_num");
		String trpecp_payment_person = jsonObject.getString("trpecp_payment_person");
		String trpecp_payment_time = jsonObject.getString("trpecp_payment_time");
		String trpecp_memo = jsonObject.getString("trpecp_memo");
		String reserve1 = jsonObject.getString("reserve1");
		String curTime = FormatUtil.getCurentDate();
		String curuser = user.getUserId();
		String inStr="Insert into trp_exceeds_children_penalty(trpecp_id,tbfi_id,org_id,trpecp_ec_description,trpecp_exceeds_children_num,trpecp_penalty,trpecp_payment_person,trpecp_payment_time,trpecp_memo,reserve1,create_by, create_time,last_modify_by, last_modify_time)"+
		"values('"+AccessDataBaseUtil.getPKUUID()+"','"+tbfi_id+"','"+org_id+"','"+trpecp_ec_description+"','"+trpecp_exceeds_children_num+"','"+trpecp_penalty+"','"+trpecp_payment_person+"','"+trpecp_payment_time+"','"+trpecp_memo+"','"+reserve1+"','"+curuser+"','"+curTime+"','"+curuser+"','"+curTime+"')";

		String sqlFam = "update tb_family_base set reserve5 = '"+reserve5+"' where tbfi_id = '"+ tbfi_id +"'";//reserve5暂时标示是否超生
		//String sqlExc = "update trp_exceeds_children_penalty set trpecp_ec_description='"+trpecp_ec_description+"',trpecp_exceeds_children_num='"+trpecp_exceeds_children_num+"',trpecp_payment_person='"+trpecp_payment_person+"',trpecp_payment_time='"+trpecp_payment_time+"',trpecp_memo='"+trpecp_memo+"' where trpecp_id = '"+ trpecp_id +"'";
		String[] sqls = {inStr,sqlFam};
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"保存成功\"";
	}
	//生成当年的超生记录
	public Object insertData(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDate();
		String curuser = user.getUserId();
		//先删除今年的记录，重新生成
		String sqldel = "delete from trp_exceeds_children_penalty where YEAR(create_time)='"+curTime.substring(0, 4)+"' and org_id in (" + user.getCurSubOrgId() + ")";
		
		String sqlinto="Insert into trp_exceeds_children_penalty(trpecp_id,tbfi_id,org_id,trpecp_ec_description,trpecp_exceeds_children_num,trpecp_penalty,trpecp_payment_person,trpecp_payment_time,reserve1,create_by, create_time,last_modify_by, last_modify_time)" +
		"select REPLACE(uuid(),'-',''),f.tbfi_id,f.org_id,'超生',f.tbfi_children_num-2,(f.tbfi_children_num-2)*"+user.getGlobalMap().get("Extra birth")+",e.tbe_name,'"+curTime+"',e.reserve1,'"+curuser+"','"+curTime+"','"+curuser+"','"+curTime+"' from tb_family_base f,tb_employee e where f.tbe_id=e.tbe_id and f.reserve5='1' and f.tbfi_children_num > 2 and e.org_id in ("+user.getCurSubOrgId()+")";

		///根据登记的情况进行生成
		String[] sqls = {sqldel,sqlinto};
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"汇总成功\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
//		String curTime = FormatUtil.getCurentDateTime();
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String trpecp_id = jsonObject.getString("trpecp_id");
		String trpecp_ec_description = jsonObject.getString("trpecp_ec_description");
		String trpecp_exceeds_children_num = jsonObject.getString("trpecp_exceeds_children_num");
		String trpecp_payment_person = jsonObject.getString("trpecp_payment_person");
		String trpecp_payment_time = jsonObject.getString("trpecp_payment_time");
		String trpecp_memo = jsonObject.getString("trpecp_memo");
		int pena = Integer.parseInt(user.getGlobalMap().get("Extra birth"))*Integer.parseInt(trpecp_exceeds_children_num);
		String sqlExc = "update trp_exceeds_children_penalty set trpecp_penalty="+pena+",trpecp_ec_description='"+trpecp_ec_description+"',trpecp_exceeds_children_num='"+trpecp_exceeds_children_num+"',trpecp_payment_person='"+trpecp_payment_person+"',trpecp_payment_time='"+trpecp_payment_time+"',trpecp_memo='"+trpecp_memo+"' where trpecp_id = '"+ trpecp_id +"'";
		AccessDataBaseUtil.executeSQLUpdate(sqlExc);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		String sqldel = "";
		for (int i = 0; i < ids.length - 1; i = i + 2) {
			sqldel = sqldel + "delete from trp_exceeds_children_penalty where trpecp_id ='"+ ids[i] + "'" + ",";
			
			sqldel = sqldel+"update tb_family_base set reserve5 = '0' where tbfi_id = '"+ ids[i+1] +"'" + ",";
		}
		//System.out.println(sqldel);
		String[] sqls = sqldel.split(",");

		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"删除成功\"";
	}

	
	/** 
	 * 描述：listAll 查看所有的人员
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn Aug 28, 2012  1:31:49 PM
	 * @author wangli
	 */
	public Object listAll(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbe_name = jsonObject.getString("tbe_name");
		String tbe_idcard = jsonObject.getString("tbe_idcard");
		//找出家庭子女数超过2个的显示出来,死亡，已离职，已退休状态,劳务用工状态的除外,女方登记
		sql = " FROM tb_employee e LEFT JOIN tb_family_base f ON e.tbe_id=f.tbe_id where e.tbe_employment_forms <> '31' and tbe_status not in('102-5','102-4','102-3') and e.org_id in (" + user.getCurSubOrgId() + ") and tbfi_children_num > 2";
		if(StringUtils.isNotBlank(tbe_name)){
			sql += " and tbe_name like '%" + tbe_name + "%'";
		}
		if(StringUtils.isNotBlank(tbe_idcard)){
			sql += " and tbe_idcard like '%" + tbe_idcard + "%'";
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		
		String countSql = "select count(*)" + sql;
		String querySql = "SELECT f.tbfi_id,e.org_id,e.tbe_name,e.tbe_idcard,e.tbe_sex,e.tbe_age,e.tbe_birthday,(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = e.tbe_nation) as tbe_nation,f.tbfi_phone,f.tbfi_address,f.tbfi_children_num,f.tbfi_peo_num,f.tbfi_children_mnum,f.tbfi_children_fnum "+sql;

		String[] fields = {"tbfi_id","org_id","tbe_name","tbe_idcard","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbfi_phone","tbfi_address","tbfi_children_num","tbfi_peo_num","tbfi_children_mnum","tbfi_children_fnum"};
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	

	/** 
	 * 描述：expData 人员导出
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn 
	 * @author wangli
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String orgIds = jsonObject.getString("orgIds");//机构ID串
		String tbe_name = jsonObject.getString("tbe_name");//姓名
		String trpecp_payment_person = jsonObject.getString("trpecp_payment_person");//缴款人
		String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证号
		String time1 = jsonObject.getString("time1");// 开始时间
		String time2 = jsonObject.getString("time2");// 结束时间

		StringBuffer buff = new StringBuffer();
		buff.append("SELECT e.reserve1,e.tbe_name,e.tbe_birthday,f.tbfi_phone,f.tbfi_address,f.tbfi_peo_num,f.tbfi_children_num,f.tbfi_children_mnum,f.tbfi_children_fnum,")
			.append("p.trpecp_ec_description,p.trpecp_exceeds_children_num,p.trpecp_penalty,p.trpecp_payment_person,p.trpecp_payment_time,p.trpecp_memo")
			.append(" from trp_exceeds_children_penalty p LEFT JOIN tb_family_base f on f.tbfi_id=p.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id where 1=1 ");
		if(StringUtils.isBlank(orgIds)){
			buff.append(" and e.org_id in (").append(user.getCurSubOrgId()).append(")");
		} else {
			buff.append(" and e.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
		}
		if(StringUtils.isNotBlank(tbe_name)){
			buff.append(" and tbe_name like '%").append(tbe_name).append("%'");
		}
		if(StringUtils.isNotBlank(trpecp_payment_person)){
			buff.append(" and trpecp_payment_person like '%").append(trpecp_payment_person).append("%'");
		}
		if(StringUtils.isNotBlank(tbe_idcard)){
			buff.append(" and tbe_idcard like '%").append(tbe_idcard).append("%'");
		}
		if(StringUtils.isNotBlank(time1)) {
			buff.append(" and trpecp_payment_time >= '").append(time1).append("'");
		}
		if(StringUtils.isNotBlank(time2)) {
			buff.append(" and trpecp_payment_time <='").append(time2).append(" 23:59:59'");
		}
		//System.out.println(buff.toString());
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/exceeds.xls";
		String file = url + "/file/exceeds.xls";
		String rfile = "jsp/app/xls/file/exceeds.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);

		if(list.size() > 0){
			XLSUtils.writeXls(list, fto, "超生信息一览表", 1);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		return "\"" + rfile + "\"";
	}
	
	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}


